package moon.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import moon.util.StringUtils;

/**
 * excel操作基础封装
 */
public class ExcelExportWrapper {
	public static final Logger LOGGER = LoggerFactory.getLogger(ExcelExportWrapper.class);
	private Workbook wb;
	private CellStyle defaultCellStyle = null; // 默认单元格样式
	private Map<String, CellStyle> cellStyleCache = new HashMap<String, CellStyle>();
	private Map<String, Short> formatCache = new HashMap<String, Short>();
	/** 用于自动列宽 */
	private Map<Sheet, Map<Integer, Integer>> columnWidth = new HashMap<Sheet, Map<Integer, Integer>>();
	private DataFormat dataFormat;
	private boolean autoColumnWidth = true;
	private Map<Class<?>, String> dataFormatCache = new HashMap<Class<?>, String>();

	public ExcelExportWrapper() {
		this("xlsx");
	}

	public ExcelExportWrapper(String type) {
		Workbook wb = null;
		if ("xls".equals(type)) {
			wb = new HSSFWorkbook();
		} else if ("xlsx".equals(type)) {
			wb = new SXSSFWorkbook(100);
			((SXSSFWorkbook) wb).setCompressTempFiles(true);
		} else {
			throw new ExcelWraperException("创建excel失败,请检查文件格式。");
		}
		init(wb);
	}

	public ExcelExportWrapper(Workbook wb) {
		init(wb);
	}

	private void init(Workbook wb) {
		try {
			if (wb != null) {
				this.wb = wb;
				dataFormat = wb.createDataFormat();
				formatCache.put("yyyy-MM-dd HH:mm:ss", dataFormat.getFormat("yyyy-MM-dd HH:mm:ss"));
				formatCache.put("yyyy-MM-dd", dataFormat.getFormat("yyyy-MM-dd"));
				formatCache.put("@", dataFormat.getFormat("@"));

				CellStyle defauleS = wb.createCellStyle();
				defauleS.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				defauleS.setWrapText(true);
				setDefaultCellStyle(defauleS);
				setDataFormat(Date.class, "yyyy-MM-dd HH:mm:ss");
				setDataFormat(CharSequence.class, "@");
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new ExcelWraperException("创建excel失败,请检查文件格式和内容。");
		}
	}

	public Workbook getWorkbook() {
		return wb;
	}

	/***
	 * 创建sheet
	 * 
	 * @param sheetName sheet名称
	 * @return
	 */
	public Sheet buildSheet(String sheetName) {
		return buildSheet(sheetName, 20);
	}

	/***
	 * 创建sheet
	 * 
	 * @param sheetName          sheet名称
	 * @param defaultColumnWidth 默认列宽
	 * @return
	 */
	public Sheet buildSheet(String sheetName, int defaultColumnWidth) {
		// 创建sheet
		if (StringUtils.isBlank(sheetName)) {
			sheetName = UUID.randomUUID().toString();
		}
		sheetName = sheetName.replaceAll("[\u0000\u0003:\\\\*\\?/\\[\\]<>\"\\|]", "");
		sheetName = sheetName.length() > 31 ? sheetName.substring(0, 28) + "..." : sheetName;
		Sheet sheet = getWorkbook().createSheet(sheetName);
		sheet.setDefaultColumnWidth(defaultColumnWidth);
		return sheet;
	}

	/**
	 * 获取已经存在的sheet
	 * 
	 * @param name sheet名称
	 * @return
	 */
	public Sheet getSheet(String name) {
		if (StringUtils.isBlank(name)) {
			throw new ExcelWraperException("sheet名称不能为空");
		}
		Sheet sheet = getWorkbook().getSheet(name);
		return sheet;
	}

	/***
	 * 获取已经存在的sheet
	 * 
	 * @param sheetIndex sheet索引
	 * @return
	 */
	public Sheet getSheet(int sheetIndex) {
		if (sheetIndex < 0) {
			throw new ExcelWraperException("sheet索引必须大于等于0");
		}
		Sheet sheet = getWorkbook().getSheetAt(sheetIndex);
		return sheet;
	}

	/**
	 * 从第一行第一列开始绘制数据
	 * 
	 * @param sheet sheet对象
	 * @param list  数据值
	 */
	public void paint(Sheet sheet, Object[] list) {
		paint(sheet, 0, 0, list, null, null);
	}

	/**
	 * 从第一行第一列开始绘制数据
	 * 
	 * @param sheet     sheet对象
	 * @param list      数据值
	 * @param cellstyle 单元格样式
	 */
	public void paint(Sheet sheet, Object[] list, CellStyle cellstyle) {
		paint(sheet, 0, 0, list, cellstyle, null);
	}

	/**
	 * 从某个位置开始绘制数据，行列起始位置索引为0
	 * 
	 * @param sheet       sheet对象
	 * @param rowIndex    从那一行开始
	 * @param columnIndex 从那一列开始
	 * @param list        数据值
	 */
	public void paint(Sheet sheet, int rowIndex, int columnIndex, Object[] list) {
		paint(sheet, rowIndex, columnIndex, list, null, null);
	}

	/**
	 * 从某个位置开始绘制数据，行列起始位置索引为0
	 * 
	 * @param sheet       sheet对象
	 * @param rowIndex    从那一行开始
	 * @param columnIndex 从那一列开始
	 * @param list        数据值
	 * @param cellstyle   单元格样式
	 */
	public void paint(Sheet sheet, int rowIndex, int columnIndex, Object[] list, CellStyle cellstyle) {
		paint(sheet, rowIndex, columnIndex, list, cellstyle, null);
	}

	/**
	 * 从某个位置开始绘制数据，行列起始位置索引为0
	 * 
	 * @param sheet       sheet对象
	 * @param rowIndex    从那一行开始
	 * @param columnIndex 从那一列开始
	 * @param list        数据值
	 * @param cellstyle   单元格样式
	 * @param formats     值展示的格式
	 */
	public void paint(Sheet sheet, int rowIndex, int columnIndex, Object[] list, CellStyle cellstyle, String[] formats) {
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		if (list != null && list.length != 0) {
			for (int i = 0; i < list.length; i++) {
				Object v = list[i];
				Cell cell = row.getCell(i + columnIndex);
				if (cell == null) {
					cell = row.createCell(i + columnIndex);
				}
				paintCell(cell, v, cellstyle, formats != null && formats.length > i && StringUtils.isNotBlank(formats[i]) ? formats[i] : "");
			}
		}
	}

	public void paintCell(Cell cell, Object val, CellStyle cellstyle, String format) {
		CellStyle deCellStyle = cellstyle;
		if (deCellStyle == null) {
			deCellStyle = defaultCellStyle;
		}
		if (val != null) {
			if (StringUtils.isNotBlank(format)) {
				deCellStyle = getFormatCellStyle(deCellStyle, format);
			} else if (Date.class.isAssignableFrom(val.getClass())) {
				deCellStyle = getFormatCellStyle(deCellStyle, dataFormatCache.get(Date.class));
			} else if (CharSequence.class.isAssignableFrom(val.getClass())) {
				deCellStyle = getFormatCellStyle(deCellStyle, dataFormatCache.get(CharSequence.class));
			}
		}
		cell.setCellStyle(deCellStyle);
		fillCellValue(cell, val);
	}

	private CellStyle getFormatCellStyle(CellStyle cellstyle, String format) {
		CellStyle deCellStyle = cellstyle;
		if (StringUtils.isNotBlank(format)) {
			String key = deCellStyle.toString() + "_" + format;
			if (cellStyleCache.containsKey(key)) {
				deCellStyle = cellStyleCache.get(key);
			} else {
				deCellStyle = buildFormtCellStyle(deCellStyle, format);
				cellStyleCache.put(key, deCellStyle);
			}
		}
		return deCellStyle;
	}

	private CellStyle buildFormtCellStyle(CellStyle copy, String format) {
		CellStyle style = copy;
		short desf = 0;
		if (formatCache.containsKey(format)) {
			desf = formatCache.get(format);
		} else {
			desf = dataFormat.getFormat(format);
			formatCache.put(format, desf);
		}
		if (desf != 0) {
			style = wb.createCellStyle();
			style.cloneStyleFrom(copy);
			style.setDataFormat(desf);
		}
		return style;
	}

	/**
	 * 为指定区域的单元格绘制样式
	 * 
	 * @param sheet     sheet对象
	 * @param region    区域
	 * @param cellstyle 单元格样式
	 */
	public void paintStyle(Sheet sheet, CellRegion region, CellStyle cellstyle) {
		if (region != null && sheet != null && cellstyle != null) {
			for (int i = region.getFirstRow(); i <= region.getFootRow(); i++) {
				Row row = sheet.getRow(i);
				if (row == null) {
					throw new ExcelWraperException("行" + i + "不存在，无法绘制样式。");
				}
				for (int j = region.getFirstCol(); j <= region.getFootCol(); j++) {
					Cell cell = row.getCell(j);
					if (cell == null) {
						throw new ExcelWraperException("单元格(" + i + "," + j + ")不存在，无法绘制样式。");
					}
					cell.setCellStyle(cellstyle);
				}
			}
		}
	}

	/**
	 * 合并区域
	 * 
	 * @param list  需要合并的所有区域
	 * @param sheet sheet对象
	 */
	public void mergeRegion(List<CellRegion> list, Sheet sheet) {
		if (!list.isEmpty()) {
			for (CellRegion cellRegion : list) {
				sheet.addMergedRegion(new CellRangeAddress(cellRegion.getFirstRow(), cellRegion.getFootRow(), cellRegion.getFirstCol(), cellRegion.getFootCol()));
			}
		}
	}

	/**
	 * 合并区域
	 * 
	 * @param cellRegion 需要合并的区域
	 * @param sheet      sheet对象
	 */
	public void mergeRegion(CellRegion cellRegion, Sheet sheet) {
		sheet.addMergedRegion(new CellRangeAddress(cellRegion.getFirstRow(), cellRegion.getFootRow(), cellRegion.getFirstCol(), cellRegion.getFootCol()));
	}

	/***
	 * 导出excel
	 * 
	 * @param os 输出流
	 */
	public void export(OutputStream os) {
		long begin = System.currentTimeMillis();
		try {
			exportBeforeHandle();
			Workbook wb = getWorkbook();
			wb.write(os);
			if (wb instanceof SXSSFWorkbook) {
				((SXSSFWorkbook) wb).dispose();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		LOGGER.info("导出excel时间：" + (System.currentTimeMillis() - begin) + "ms");
	}

	/**
	 * 非字符串值的单元格赋值
	 * 
	 * @param cell
	 * @param o
	 */
	private void fillCellValue(Cell cell, Object o) {
		int length = 20;
		try {
			if (o != null) {
				if (o instanceof Integer) {
					cell.setCellValue(Double.parseDouble("" + o));
					length = o.toString().getBytes("gbk").length;
				} else if (o instanceof Float) {
					cell.setCellValue(Double.parseDouble("" + o));
					length = o.toString().getBytes("gbk").length;
				} else if (o instanceof Double) {
					cell.setCellValue(Double.parseDouble("" + o));
					length = o.toString().getBytes("gbk").length;
				} else if (o instanceof Long) {
					cell.setCellValue(Double.parseDouble("" + o));
					length = o.toString().getBytes("gbk").length;
				} else if (o instanceof Short) {
					cell.setCellValue(Double.parseDouble("" + o));
					length = o.toString().getBytes("gbk").length;
				} else if (o instanceof Boolean) {
					cell.setCellValue(Boolean.parseBoolean("" + o));
					length = o.toString().getBytes("gbk").length;
				} else if (o instanceof RichTextString) {
					cell.setCellValue((RichTextString) o);
					length = ((RichTextString) o).getString().getBytes("gbk").length;
				} else if (Date.class.isAssignableFrom(o.getClass())) {
					cell.setCellValue((Date) o);
				} else if (o instanceof ExcelFormula) {
					cell.setCellFormula(((ExcelFormula) o).getFormula());
					length = 0;
				} else if (o instanceof BigDecimal) {
					cell.setCellValue(((BigDecimal) o).doubleValue());
					length = o.toString().getBytes("gbk").length;
				} else {
					cell.setCellValue(o.toString());
					length = o.toString().getBytes("gbk").length;
				}
			} else {
				cell.setCellValue("");
			}
		} catch (Exception e) {
			e.printStackTrace();
			cell.setCellValue("");
		}
		setMaxWidth(cell.getSheet(), cell.getColumnIndex(), length);
	}

	/**
	 * 默认单元格样式
	 * 
	 * @return
	 */
	public CellStyle getDefaultCellStyle() {
		return defaultCellStyle;
	}

	public boolean isAutoColumnWidth() {
		return autoColumnWidth;
	}

	public void setAutoColumnWidth(boolean autoColumnWidth) {
		this.autoColumnWidth = autoColumnWidth;
	}

	private void setMaxWidth(Sheet sheet, int i, int length) {
		if (isAutoColumnWidth()) {
			try {
				Map<Integer, Integer> rowWidth = columnWidth.get(sheet);
				if (rowWidth == null) {
					rowWidth = new HashMap<Integer, Integer>();
					columnWidth.put(sheet, rowWidth);
				}
				Integer width = rowWidth.get(i);
				if (width != null) {
					if (length > width) {
						rowWidth.put(i, length);
					}
				} else {
					rowWidth.put(i, length);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private void exportBeforeHandle() {
		if (isAutoColumnWidth()) {
			for (Map.Entry<Sheet, Map<Integer, Integer>> e : columnWidth.entrySet()) {
				Sheet sheet = e.getKey();
				Map<Integer, Integer> rowWidth = e.getValue();
				if (rowWidth != null && !rowWidth.isEmpty()) {
					for (Map.Entry<Integer, Integer> row : rowWidth.entrySet()) {
						int deswidth = ((row.getValue() * 7 + 5) / 7 + 1);
						sheet.setColumnWidth(row.getKey(), (deswidth > 255 ? 255 : deswidth) * 256);
					}
				}
			}
		}
	}

	public void setDataFormat(Class<?> c, String format) {
		dataFormatCache.put(c, format);
	}

	public void setDefaultCellStyle(CellStyle cellStyle) {
		this.defaultCellStyle = cellStyle;
		getFormatCellStyle(cellStyle, "yyyy-MM-dd HH:mm:ss");
		getFormatCellStyle(cellStyle, "yyyy-MM-dd");
		getFormatCellStyle(cellStyle, "@");
	}
}
